package com.jsy.common.util;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.SQLInsertStatement;
import com.alibaba.druid.sql.ast.statement.SQLUpdateSetItem;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerInsertStatement;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerUpdateStatement;
import com.alibaba.druid.util.JdbcConstants;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author gates.huang
 * @Description: SQLServer解析
 * @Date 2021/08/21
 */
public class SqlServerParser {

    /**
     * 解析sql语句
     *
     * @param statement
     * @return
     */
    public static List<List<Object>> parse(String statement) {
        // 使用druid解析语句
        // 第一个参数为SQL语句
        // 第二个参数为解析的数据库类型
        List<SQLStatement> statementList  = SQLUtils.parseStatements(statement, JdbcConstants.SQL_SERVER);
        // 单语句解析，只有一条数据
        if (!statement.isEmpty()) {
            SQLStatement sqlStatement = statementList.get(0);
            // 插入语句解析
            if (sqlStatement instanceof SQLServerInsertStatement) {
                // 转换
                SQLServerInsertStatement insertStatement = (SQLServerInsertStatement) sqlStatement;
                // 获取列名
                List<SQLExpr> columns = insertStatement.getColumns();
                List<String> columnsName = new ArrayList<>(columns.size());
                for (SQLExpr column : columns) {
                    columnsName.add(((SQLIdentifierExpr) column).getName());
                }
                System.out.println(columnsName);
                // 获取值
                List<SQLInsertStatement.ValuesClause> valuesList = insertStatement.getValuesList();
                List<List<Object>> dataList = new ArrayList<>();
                for (SQLInsertStatement.ValuesClause valuesClause : valuesList) {
                    List<SQLExpr> values = valuesClause.getValues();
                    List<Object> data = new ArrayList<>(columnsName.size());
                    for (SQLExpr value : values) {
                        data.add(getValue(value));
                    }
                    dataList.add(data);
                }
                System.out.println(dataList);
                // 获取表名
                System.out.println(insertStatement.getTableName().getSimpleName());
                return dataList;
            } else if (sqlStatement instanceof SQLServerUpdateStatement) {
                // 更新语句解析
                SQLServerUpdateStatement updateStatement = (SQLServerUpdateStatement) sqlStatement;
                // 获取更新的值和内容
                List<SQLUpdateSetItem> items = updateStatement.getItems();
                Map<String, Object> updateMap = new HashMap<>(items.size());
                for (SQLUpdateSetItem item : items) {
                    updateMap.put(((SQLIdentifierExpr) item.getColumn()).getName(), getValue(item.getValue()));
                }
                System.out.println(updateMap);
                // 获取条件，条件比较复杂，需根据实际情况自行提取
                SQLBinaryOpExpr where = (SQLBinaryOpExpr) updateStatement.getWhere();
                System.out.println(where);
                // 获取表名
                System.out.println(updateStatement.getTableName().getSimpleName());
                return null;
            }
        }
        return null;
    }


    private static Object getValue(SQLExpr value) {
        // TODO 判断更多的种类
        if (value instanceof SQLIntegerExpr) {
            // 值是数字
            return ((SQLIntegerExpr) value).getNumber();
        } else if (value instanceof SQLCharExpr) {
            // 值是字符串
            return ((SQLCharExpr) value).getText();
        }
        return null;
    }

    public static void main(String[] args) throws Exception {
//        parse("update test set status='P' where id=20");
//        parse("insert into test (id,status,name,ce,acc) values (29,'P','lll','sxsx','Arferwg')");
        File file = new File("F:\\汇思工作\\工作文档\\屈臣氏hr学习地图\\历史数据迁移\\文件处理\\5分");
        if (file.isDirectory()){
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                SqlFileToExcel(files[i].getAbsolutePath(),"F:\\汇思工作\\工作文档\\屈臣氏hr学习地图\\历史数据迁移\\文件处理\\excel\\"+i+"-out.xlsx");
            }
        }
//        SqlFileToExcel("F:\\汇思工作\\工作文档\\屈臣氏hr学习地图\\历史数据迁移\\文件处理\\5分\\4-out.sql","F:\\汇思工作\\工作文档\\屈臣氏hr学习地图\\历史数据迁移\\文件处理\\excel\\0-out.xlsx");
    }

    public static void createCell(XSSFCellStyle style, XSSFRow row0, int i, String s) {
        XSSFCell cell_00 = row0.createCell(i);
        cell_00.setCellStyle(style);
        cell_00.setCellValue(s);
    }

    public static void SqlFileToExcel(String filePath,String path)throws Exception{
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        XSSFSheet sheet = workbook.createSheet("sheet");

        XSSFRow row0 = sheet.createRow(0);
        createCell(style, row0, 0, "MOD_MOD_ID_ROOT");
        createCell(style, row0, 1, "MOD_RES_ID");
        createCell(style, row0, 2, "COS_RES_ID");
        createCell(style, row0, 3, "ITM_ID");
        createCell(style, row0, 4, "USR_ID");
        createCell(style, row0, 5, "工号");
        createCell(style, row0, 6, "姓名");
        createCell(style, row0, 7, "入职时间");
        createCell(style, row0, 8, "离职时间");
        createCell(style, row0, 9, "项目");
        createCell(style, row0, 10, "课程评估问卷标题");
        createCell(style, row0, 11, "课程/品牌");
        createCell(style, row0, 12, "班级名称");
        createCell(style, row0, 13, "培训开始日期");
        createCell(style, row0, 14, "培训结束日期");
        createCell(style, row0, 15, "开始时间");
        createCell(style, row0, 16, "结束时间");
        createCell(style, row0, 17, "讲师工号");
        createCell(style, row0, 18, "讲师姓名");
        createCell(style, row0, 19, "课程时长");
        createCell(style, row0, 20, "测试分数");
        createCell(style, row0, 21, "签到记录");
        createCell(style, row0, 22, "题目1");
        createCell(style, row0, 23, "1平均分");
        createCell(style, row0, 24, "题目2");
        createCell(style, row0, 25, "题目3");
        createCell(style, row0, 26, "题目4");
        createCell(style, row0, 27, "题目5");
        createCell(style, row0, 28, "题目6");
        createCell(style, row0, 29, "题目7");
        createCell(style, row0, 30, "2-7平均分");
        createCell(style, row0, 31, "题目8");
        createCell(style, row0, 32, "题目9");
        createCell(style, row0, 33, "题目10");
        createCell(style, row0, 34, "题目11");
        createCell(style, row0, 35, "题目12");
        createCell(style, row0, 36, "8-12平均分");
        createCell(style, row0, 37, "题目13");
        createCell(style, row0, 38, "题目14");
        createCell(style, row0, 39, "题目15");
        createCell(style, row0, 40, "13-15平均分");
        createCell(style, row0, 41, "1-15平均分");
        createCell(style, row0, 42, "题目16");
        createCell(style, row0, 43, "题目17");
        createCell(style, row0, 44, "课程类型");
        createCell(style, row0, 45, "当时职位");
        createCell(style, row0, 46, "当时级别");
        createCell(style, row0, 47, "当时类型");
        createCell(style, row0, 48, "当时店铺/部门");
        XSSFRow row=null;
        String id=null;
        int count=0;
        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new FileReader(filePath));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return;
        }
        String header = null;
        try {
            header = reader.readLine();
        } catch (IOException e) {
            e.printStackTrace();
        }

//        String sql = "INSERT INTO xianshang(\"工号\",\"姓名\",\"入职时间\",\"离职时间\",\"项目\",\"课程\",\"章节(单元)\",\"课件/视频/试卷名称等\",\"考核分数\",\"推送/发布日期\",\"学习日期\",\"标准时长分钟\",\"实际学习时长\",\"完成状态\",\"培训方式\",\"当时职位\",\"当时级别\",\"当时类型\",\"当时店铺/部门\",RES_ID,ITM_ID)\n";
        String sql = "INSERT INTO xianxia(MOD_MOD_ID_ROOT,MOD_RES_ID,COS_RES_ID,ITM_ID,USR_ID,\"工号\",\"姓名\",\"入职时间\",\"离职时间\",\"项目\",\"课程评估问卷标题\",\"课程/品牌\",\"班级名称\",\"培训开始日期\",\"培训结束日期\",\"开始时间\",\"结束时间\",\"讲师工号\",\"讲师姓名\",\"课程时长\",\"测试分数\",\"签到记录\",\"题目1\",\"1平均分\",\"题目2\",\"题目3\",\"题目4\",\"题目5\",\"题目6\",\"题目7\",\"2-7平均分\",\"题目8\",\"题目9\",\"题目10\",\"题目11\",\"题目12\",\"8-12平均分\",\"题目13\",\"题目14\",\"题目15\",\"13-15平均分\",\"1-15平均分\",\"题目16\",\"题目17\",\"课程类型\",\"当时职位\",\"当时级别\",\"当时类型\",\"当时店铺/部门\")\n";
        int number =1;
        if (header != null) {
            String line = null;
            while ((line = reader.readLine()) != null) {
                    if (line.contains("VALUES")) {
                        List<List<Object>> dataList = null;
                        try {
                            dataList = parse(sql + line);
                        } catch (Exception e) {
                            String temp =sql + line + reader.readLine();
                            try {
                                dataList = parse(temp);
                            } catch (Exception ex) {
                                System.out.println(temp);
                                ex.printStackTrace();
                            }
                        }
                        if (dataList!=null&&dataList.size()==1){
                            row = sheet.createRow(number++);

                            System.out.println("====================="+number+"===============");
                            List<Object> objects = dataList.get(0);
                            for (int i = 0; i < objects.size(); i++) {
                                Object o = objects.get(i);
                                if (o!=null){
                                    createCell(style, row, i, objects.get(i).toString());
                                }else {
                                    createCell(style, row, i, "");
                                }
                            }
                        }
                    }
            }
        }
        if (path!=null){
            File file = new File(path);
            FileOutputStream fout = new FileOutputStream(file);
            workbook.write(fout);
            fout.close();
        }
    }

}